﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using CQRS.Reports.SqlQueryBuilder;

namespace CQRS.Reports
{
    public class MsSqlReportDatabaseCreator : IReportDatabaseCreator
    {
        private List<Type> _dtos = new List<Type>();
        private readonly ISqlCreateTableStatementBuilder _sqlCreateBuilder = new SqlTableCreateStatementBuilder();
        private string _connectionString;
        private string _dataBaseName;
        private string _serverConnectionString = "Server=.\\SQLEXPRESS;Integrated security=SSPI;database=master";
        #region IReportDatabaseCreator Members

        public void Create(Assembly reportsAssembly)
        {
            var stringBuilder = new SqlConnectionStringBuilder();
            stringBuilder.DataSource = @".\SQLEXPRESS";
            stringBuilder.InitialCatalog = _dataBaseName = "DemoCQRSApplication";
            stringBuilder.ConnectTimeout = 10;
            stringBuilder.IntegratedSecurity = true;
            _connectionString = stringBuilder.ConnectionString;

            _dtos = reportsAssembly.GetTypes()
                .Where(x => x.BaseType == typeof (Report))
                .ToList();
            CreateDatabaseSchema();
        }

        #endregion

        protected void CreateDatabaseSchema()
        {
            if (!DatabaseExists())
            {
                CreateDatabase();
                foreach (Type dto in _dtos)
                {
                    if (!TableExists(dto.Name))
                        CreateTable(dto);
                }
            }
        }

        private void CreateTable(Type dto)
        {
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                sqlConnection.Open();
                using (
                    var sqlCmd = new SqlCommand(_sqlCreateBuilder.CreateSqlCreateStatementFromDto(dto), sqlConnection))
                {
                    sqlCmd.ExecuteNonQuery();
                }
            }
        }

        private bool TableExists(string name)
        {
            string cmdText = string.Format("SELECT Count(*) FROM sysobjects WHERE xtype='u' AND name='{0}'", name);
            using (var sqlConnection = new SqlConnection(_serverConnectionString))
            {
                sqlConnection.Open();
                using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
                {
                    int tableCount = (int) sqlCmd.ExecuteScalar();
                    return (tableCount > 0);
                }
            }
        }

        private void CreateDatabase()
        {
            var connection = new SqlConnection(_serverConnectionString);

            string createDatabaseCommand = string.Format("CREATE DATABASE {0} ON PRIMARY " +
                                                         "(NAME = {0}, " +
                                                         "FILENAME = 'C:\\masterat\\masterWork\\{0}.mdf', " +
                                                         "SIZE = 20MB, MAXSIZE = 200MB, FILEGROWTH = 10%) " +
                                                         "LOG ON (NAME = {0}_Log, " +
                                                         "FILENAME = 'C:\\masterat\\masterWork\\{0}.ldf', " +
                                                         "SIZE = 1MB, " +
                                                         "MAXSIZE = 5MB, " +
                                                         "FILEGROWTH = 10%)", _dataBaseName);

            var myCommand = new SqlCommand(createDatabaseCommand, connection);
            try
            {
                connection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Could not connect to the database with the following exception {0}", ex.Message);
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        private bool DatabaseExists()
        {
            string cmdText = string.Format("select Count(*) from master.dbo.sysdatabases where name=\'{0}\'", _dataBaseName);
            using (var sqlConnection = new SqlConnection(_serverConnectionString))
            {
                sqlConnection.Open();
                using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
                {
                    var scalar = (int) sqlCmd.ExecuteScalar();
                    return scalar > 0;
                }
            }
        }
    }
}